﻿Option Explicit On
Option Strict On
Imports System.Data
Imports System.Text
Imports System.Configuration
Imports MySql.Data.MySqlClient

Public Class frmOrder

    Dim Conn As New MySqlConnection()
    Dim com As New MySqlCommand()
    Dim sb As New StringBuilder()
    Dim dr As MySqlDataReader
    Dim dt As DataTable
    Dim tr As MySqlTransaction

    Private Sub btnBrowser_B_Click(sender As Object, e As EventArgs) Handles btnBrowser_B.Click
        Dim frm As New frmBorrowserShop()
        frm.StartPosition = FormStartPosition.CenterScreen
        frm.ID = txtID.Text.Trim()
        If frm.ShowDialog() = Windows.Forms.DialogResult.OK Then
            txtID.Text = frm.ID
            lbName.Text = frm.Name_s
        End If
    End Sub

    Private Sub btnBrowser_Click(sender As Object, e As EventArgs) Handles btnBrowser.Click
        Dim frm As New frmBrowserEqu()
        frm.StartPosition = FormStartPosition.CenterScreen
        frm.ID = txtID.Text.Trim()
        If frm.ShowDialog() = Windows.Forms.DialogResult.OK Then
            txtID_eup.Text = frm.ID
            lbName_equ.Text = frm.Name_equ
        End If
    End Sub

    Private Sub txtID_KeyDown(sender As Object, e As KeyEventArgs) Handles txtID.KeyDown
        If txtID.Text.Trim() = "" Then Exit Sub
        If e.KeyCode = Keys.Enter Then
            sb.Remove(0, sb.Length)
            sb.Append("SELECT * FROM Shop WHERE id_shop = @id")
            Dim sql As String = sb.ToString()
            With com
                .CommandText = sql
                .CommandType = CommandType.Text
                .Connection = Conn
                .Parameters.Clear()
                .Parameters.Add("@id", MySqlDbType.Int32).Value = CInt(txtID.Text.Trim())
                dr = .ExecuteReader()
                If dr.HasRows Then
                    dt = New DataTable()
                    dt.Load(dr)
                    txtID.Text = dt.Rows(0)("id_shop").ToString()
                    lbName.Text = dt.Rows(0)("Name").ToString()
                End If
                dr.Close()
            End With
        End If
    End Sub

    Private Sub frmOrder_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim strConn As String = ConfigurationManager.ConnectionStrings("equ").ConnectionString
        If Conn.State = ConnectionState.Open Then
            Conn.Close()
        End If
        Conn.ConnectionString = strConn
        Conn.Open()
        lsvItem.Columns.Add("รหัสอุปกรณ์", 100, HorizontalAlignment.Left)
        lsvItem.Columns.Add("ชื่ออุปกรณ์", 250, HorizontalAlignment.Left)
        lsvItem.Columns.Add("จำนวน", 100, HorizontalAlignment.Right)
        lsvItem.View = View.Details
        lsvItem.GridLines = True
        lsvItem.FullRowSelect = True
        ClearItem()
    End Sub

    Private Sub txtID_eup_KeyDown(sender As Object, e As KeyEventArgs) Handles txtID_eup.KeyDown
        If txtID_eup.Text.Trim() = "" Then Exit Sub
        If e.KeyCode = Keys.Enter Then
            sb.Remove(0, sb.Length)
            sb.Append("SELECT * FROM item WHERE idItem = @id")
            Dim sql As String = sb.ToString()
            With com
                .CommandText = sql
                .CommandType = CommandType.Text
                .Connection = Conn
                .Parameters.Clear()
                .Parameters.Add("@id", MySqlDbType.Int32).Value = CInt(txtID_eup.Text.Trim())
                dr = .ExecuteReader()
                If dr.HasRows Then
                    dt = New DataTable()
                    dt.Load(dr)
                    txtID_eup.Text = dt.Rows(0)("idItem").ToString()
                    lbName_equ.Text = dt.Rows(0)("Name").ToString()
                End If
                dr.Close()
                txtAmount.Focus()
            End With
        End If
    End Sub

    Private Sub btnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click
        lsvItem.Clear()
    End Sub

    Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
        If txtID_eup.Text.Trim() = "" Or lbName_equ.Text.Trim() = "" Then
            txtID_eup.Focus()
            Exit Sub
        End If
        If CInt(txtAmount.Text.Trim()) = 0 Then
            txtAmount.Focus()
            Exit Sub
        End If
        Dim i As Integer
        Dim lvi As ListViewItem
        Dim itemID As Integer = 0
        For i = 0 To lsvItem.Items.Count - 1
            itemID = CInt(lsvItem.Items(i).SubItems(0).Text)
            If CInt(txtID_eup.Text.Trim()) = itemID Then
                MessageBox.Show("คุณเลือกอุปกรณ์ซ้ำกัน กรุณาเลือกใหม่ !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
                ClearItem()
                txtID_eup.Focus()
                txtID_eup.SelectAll()
                Exit Sub
            End If
        Next
        Dim anyData() As String
        anyData = New String() { _
            txtID_eup.Text, _
            lbName_equ.Text, _
            txtAmount.Text _
        }
        lvi = New ListViewItem(anyData)
        lsvItem.Items.Add(lvi)
        ClearItem()
        btnSave.Enabled = True
        txtID_eup.Focus()
    End Sub

    Private Sub ClearItem()
        txtID_eup.Text = ""
        lbName_equ.Text = ""
        txtAmount.Text = ""
    End Sub

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        If txtID.Text.Trim() = "" Then
            MessageBox.Show("กรุณาป้อนรหัสผู้ใช้ !!!", "คำเตือน", MessageBoxButtons.OK, MessageBoxIcon.Information)
            txtID.Focus()
            Exit Sub
        End If
        If lsvItem.Items.Count > 0 Then
            If MessageBox.Show("คุณต้องการบันทึกรายการเบิกอุปกรณ์ ใช่หรือไม่ ?", "คำยืนยัน", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then
                tr = Conn.BeginTransaction()
                sb.Remove(0, sb.Length)
                sb.Append("INSERT INTO `order`(`idOrder`, `date`, `id_shop`) VALUES (null,@date,@id) ")
                Dim sql As String = sb.ToString()

                With com
                    .CommandText = sql
                    .CommandType = CommandType.Text
                    .Connection = Conn
                    .Transaction = tr
                    .Parameters.Clear()
                    .Parameters.Add("@id", MySqlDbType.Int32).Value = txtID.Text.Trim()
                    .Parameters.Add("@date", MySqlDbType.Date).Value = Date.Today
                    .ExecuteNonQuery()
                End With
                sb.Remove(0, sb.Length)
                sb.Append("SELECT MAX( `idOrder` ) as maxID FROM `order`  ")
                sql = sb.ToString()

                Dim LastID As Integer
                com.CommandType = CommandType.Text
                com.CommandText = sql
                com.Connection = Conn
                dr = com.ExecuteReader()
                If dr.HasRows Then
                    dr.Read()
                    LastID = dr.GetInt32(dr.GetOrdinal("maxID"))
                Else
                    LastID = 10000
                End If
                dr.Close()

                Dim i As Integer
                For i = 0 To lsvItem.Items.Count - 1
                    sb.Remove(0, sb.Length)
                    sb.Append("INSERT INTO ordrdetail (id_order,id_item,qty) ")
                    sb.Append("VALUES (@id_o,@id_i,@qty)")
                    sql = sb.ToString()
                    With com
                        .CommandText = sql
                        .Parameters.Clear()
                        .Parameters.Add("@id_o", MySqlDbType.Int32).Value = LastID
                        .Parameters.Add("@id_i", MySqlDbType.Int32).Value = CInt(lsvItem.Items(i).SubItems(0).Text)
                        .Parameters.Add("@qty", MySqlDbType.Int32).Value = CInt(lsvItem.Items(i).SubItems(2).Text)
                        .ExecuteNonQuery()
                    End With
                Next
                tr.Commit()
                MessageBox.Show("บันทึกรายการเบิกเรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
                lsvItem.Clear()
                ClearItem()
                txtID.Focus()
            End If
        End If
    End Sub
End Class